﻿using System.Data;
using MySql.Data.MySqlClient;

namespace Common
{
    public class MySqlCommon
    {
        public static readonly MySqlCommon Instance = SingleTon<MySqlCommon>.Instance;

        public MySqlCommon()
        {
            //默认的连接字符串，可后期修改
            ConnStr = "Server=192.168.1.100;Database=yymoney;User=root;Password=4452406;";
        }

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public string ConnStr { get; set; }

        /// <summary>
        /// 获得一个数据库连接
        /// </summary>
        /// <returns>返回一个数据库连接</returns>
        public MySqlConnection GetMySqlConnection()
        {
            MySqlConnection conn = new MySqlConnection(ConnStr);
            conn.Open();
            return conn;
        }

        /// <summary>
        /// 使用现有的SQL事务执行一个sql命令（不返回数据集）
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>返回受影响的行数</returns>
        public int ExecuteNonQuery(string sql)
        {
            using (MySqlConnection conn = GetMySqlConnection())
            {
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 执行命令，返回第一行第一列的值
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>返回Object对象</returns>
        public object ExecuteScalar(string sql)
        {
            using (MySqlConnection conn = GetMySqlConnection())
            {
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    return cmd.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// 执行命令，返回MySqlDataReader对象
        /// MySqlDataReader对象使用完后必须Close以释放MySqlConnection资源
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>返回MySqlDataReader对象</returns>
        public MySqlDataReader ExecuteReader(string sql)
        {
            using (MySqlConnection conn = GetMySqlConnection())
            {
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    return cmd.ExecuteReader();
                }
            }
        }

        /// <summary>
        /// 执行命令，返回DataSet对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>返回DataSet对象</returns>
        public DataSet GetDataSet(string sql)
        {
            using (MySqlConnection conn = GetMySqlConnection())
            {
                using (MySqlDataAdapter da = new MySqlDataAdapter(sql, conn))
                {
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    return ds;
                }
            }
        }

        /// <summary>
        /// 执行命令，返回DataTable对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>返回DataTable对象</returns>
        public DataTable GetTable(string sql)
        {
            using (MySqlConnection conn = GetMySqlConnection())
            {
                using (MySqlDataAdapter da = new MySqlDataAdapter(sql, conn))
                {
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    return dt;
                }
            }
        }
    }
}
